package com.ets.Codes_DAO;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import com.ets.common.DateUtils;
import com.ets.database.DB_Pipe;

/*------------------------------------------------------------------------------
 The <big><b>CodeTypeDB</b></big> class implements the reader & writer methods
 of the <b>CodeTypeDAO</b> interface to provide the data access to the
 <big><b>CodeType</b></big> table
 <br><br>
 @author D.Merkling - 1:33:38 PM - Aug 22, 2011
 <br><br>
 @version 1.2  
------------------------------------------------------------------------------*/
public class CodeTypeDB extends DB_Pipe implements CodeTypeDAO
  {
   public CodeTypeDB ()
	 {
	 }

   /*------------------------------------------------------------------------------
	CodeType reader methods
   ------------------------------------------------------------------------------*/
   
   public boolean existsCodeType (String Sys, String Sgmt)
     {
	  try
		{
	     String exists = "SELECT Sys, "      +
		                        "Sgmt "      +
				           "FROM cd_type "   +
				          "WHERE Sys  = ? "  +
                            "AND Sgmt = ?";
         DB_Pipe              = DB_Connect();
	     PreparedStatement ps = DB_Pipe.prepareStatement(exists);
		 ps.setString(1, Sys);
		 ps.setString(2, Sgmt);
		 ResultSet         rs = ps.executeQuery();
		 if (rs.next())
		   {
		    rs.close();
		    ps.close();
		    return true;
		   }
		 else
		   {
		    rs.close();
		    ps.close();
		    return false;
		   }
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return false;
		}
	  finally
		{
		 DB_Disconnect(DB_Pipe);
		}
     }
   
   public CodeType getCodeType (String Sys, String Sgmt)
	 {
	  String tmpSys  = Sys.toUpperCase();
	         tmpSys  = tmpSys.trim();
	  String tmpSgmt = Sgmt.toUpperCase();
             tmpSgmt = tmpSgmt.trim();
	  try
		{
		 String selCodeType = "SELECT Sys, "     +
		                             "Sgmt, "    +
		                             "Desc, "    +
		                             "Len, "     +
		                             "YN_Num, "  +
		                             "YN_UCase " +
				                "FROM cd_typ "   +
				               "WHERE Sys  = ?"  +
				               "  AND Sgmt = ?";
         DB_Pipe              = DB_Connect();
		 PreparedStatement ps = DB_Pipe.prepareStatement(selCodeType);
		 ps.setString(1, tmpSys);
		 ps.setString(2, tmpSgmt);
		 ResultSet rs = ps.executeQuery();
		 if (rs.next())
		   {
		    String Desc     = rs.getString("Desc");
		    int    Len      = rs.getInt("Len");
	        String tmpYN    = rs.getString("YN_Num");
	        char   YN_Num   = tmpYN.charAt(0);
	        tmpYN           = rs.getString("YN_UCase");
	        char   YN_UCase = tmpYN.charAt(0);
		    CodeType ct     = new CodeType(Sys, Sgmt, Desc, Len, YN_Num, YN_UCase);
		    rs.close();
		    ps.close();
		    return ct;
		   }
		 else
		   return null;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return null;
		}
	  finally
		{
		 DB_Disconnect(DB_Pipe);
		}
	 }
   
   public ArrayList<CodeType> getCodeTypes ()
	 {
	  try
		{
		 ArrayList<CodeType> cts = new ArrayList<CodeType>();
		 String query = "SELECT Sys, "        +
		                       "Sgmt, "       +
		                       "Desc, "       +
		                       "Len, "        +
		                       "YN_Num, "     +
		                       "YN_UCase "    +
			 			  "FROM cd_typ "      +
					     "ORDER BY Sys ASC, " +
					              "Sgmt ASC";
         DB_Pipe              = DB_Connect();
		 PreparedStatement ps = DB_Pipe.prepareStatement(query);
		 ResultSet         rs = ps.executeQuery();
		 while(rs.next())
		   {
		    String Sys      = rs.getString("Sys");
		    String Sgmt     = rs.getString("Sgmt");
		    String Desc     = rs.getString("Desc");
		    int    Len      = rs.getInt("Len");
		    String tmpYN    = rs.getString("YN_Num");
		    char   YN_Num   = tmpYN.charAt(0);
		    tmpYN           = rs.getString("YN_UCase");
		    char   YN_UCase = tmpYN.charAt(0);
		    CodeType ct     = new CodeType(Sys, Sgmt, Desc, Len, YN_Num, YN_UCase);
		    cts.add(ct);
		   }
		 rs.close();
		 ps.close();
		 return cts;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();  // for debugging
		 return null;
		}
	  finally
		{
		 DB_Disconnect(DB_Pipe);
		}
	 }

   /*------------------------------------------------------------------------------
	CodeType writer methods
   ------------------------------------------------------------------------------*/
   
   public boolean addCodeType (CodeType ct)
	 {
	  try
		{
		 String insert   = "INSERT "                    +
		                     "INTO cd_typ (Sys, "       +
		                                  "Sgmt, "      +
		                                  "Desc, "      +
		                                  "Len, "       +
		                                  "YN_Num, "    +
		                                  "YN_UCase, "  +
		                                  "TMS_Crtd, "  +
		                                  "User_Crtd, " +
		                                  "TMS_Updt, "  +
		                                  "User_Updt) " +
				           "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
         DB_Pipe              = DB_Connect();
		 PreparedStatement ps = DB_Pipe.prepareStatement(insert);
	     Timestamp         ts = DateUtils.getTmstmp();
		 ps.setString(1, ct.getSys());
		 ps.setString(2, ct.getSgmt());
		 ps.setInt(3, ct.getLen());
		 byte tmpYN = (byte) ct.getYN_Num();
		 ps.setByte(4, tmpYN);
		 tmpYN      = (byte) ct.getYN_UCase();
		 ps.setByte(5, tmpYN);
		 ps.setTimestamp(6, ts);
		 ps.setString(7, "USER0001");
		 ps.setTimestamp(8, ts);
		 ps.setString(9, "USER9999");
		 ps.executeUpdate();
		 ps.close();
		 return true;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return false;
		}
	  finally
		{
		 DB_Disconnect(DB_Pipe);
		}
	 }

   public boolean deleteCodeType (CodeType ct)
	 {
	  try
		{
		 String delete = "DELETE "          +
		                   "FROM cd_typ "   +
                          "WHERE Sys  = ?"  +
                          "  AND Sgmt = ?";
         DB_Pipe              = DB_Connect();
		 PreparedStatement ps = DB_Pipe.prepareStatement(delete);
		 ps.setString(1, ct.getSys());
		 ps.setString(2, ct.getSgmt());
		 ps.executeUpdate();
		 ps.close();
		 return true;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return false;
		}
	  finally
		{
		 DB_Disconnect(DB_Pipe);
		}
	 }

   public boolean updateCodeType (CodeType ct)
	 {
	  try
		{
		 String update = "UPDATE cd_typ "         +
		                    "SET Desc      = ?, " +
		                        "Len       = ?, " +
		                        "YN_Num    = ?, " +
		                        "YN_UCase  = ?, " +
		                        "TMS_Updt  = ?, " +
					            "User_Updt = ? "  +
                          "WHERE Sys  = ?"  +
                          "  AND Sgmt = ?";
         DB_Pipe              = DB_Connect();
		 PreparedStatement ps = DB_Pipe.prepareStatement(update);
	     Timestamp         ts = DateUtils.getTmstmp();
		 ps.setString(1, ct.getDesc());
		 ps.setInt(2, ct.getLen());
		 byte tmpYN = (byte) ct.getYN_Num();
		 ps.setByte(3, tmpYN);
		 tmpYN      = (byte) ct.getYN_UCase();
		 ps.setByte(4, tmpYN);
		 ps.setTimestamp(5, ts);
		 ps.setString(6, "USER8888");
		 ps.executeUpdate();
		 ps.close();
		 return true;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
	 	 return false;
		}
	  finally
		{
		 DB_Disconnect(DB_Pipe);
		}
	 }
  }
